/*
 * Copyright (c) 2011 Anthony Vandikas
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation 
 * files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, 
 * modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software 
 * is furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF 
 * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY 
 * CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE 
 * OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
 */

#include "stdafx.h"
#include "DatabaseHistory.h"
#include "HistoryModel.h"

DatabaseHistory::DatabaseHistory()
{
	database = createDatabase(
		"history",
		// The format for the database is equivilant to that of
		// a flattened entry structure:
		"dateTime INTEGER, " // dateTime
		"studentName TEXT, " // student
		"studentId TEXT, "
		"studentPassword TEXT, "
		"itemName TEXT, " // item
		"itemNumber INTEGER");
}

void DatabaseHistory::clear()
{
	QSqlQuery query("DELETE FROM history", database);
	query.exec();

	emit modified();
}

void DatabaseHistory::record(const Student &student, const Item &item)
{
	QSqlQuery query("INSERT INTO history VALUES(:dateTime, :studentName, :studentId, :studentPassword, :itemName, :itemNumber)", database);

	// Date/time is stored as msecs since (unix) epoch - integers make for easy storage and comparison.
	query.bindValue(":dateTime", QDateTime::currentMSecsSinceEpoch());
	query.bindValue(":studentName", student.getName());
	query.bindValue(":studentId", student.getIdentifier());
	query.bindValue(":studentPassword", student.getPassword());
	query.bindValue(":itemName", item.getName());
	query.bindValue(":itemNumber", item.getNumber());

	query.exec();

	emit modified();
}

QList<Entry> DatabaseHistory::filter(const QDate &minDate, const QDate &maxDate, const QStringList &studentKeywords, const QStringList &itemKeywords)
{
	auto query = createFilterQuery(minDate, maxDate, studentKeywords, itemKeywords);
	query.exec();

	return extractOutput(query);
}

QSqlQuery DatabaseHistory::createFilterQuery(const QDate &minDate, const QDate &maxDate, const QStringList &studentKeywords, const QStringList &itemKeywords) const
{
	// This might be a little confusing, so the algorithm is as such:
	// 1. Create an SQL query for retrieving all entries where:
	//    1. The date recorded is between min/maxDate.
	//	  2. The string representation of the student contains all of the student keywords.
	//    3. The string representation of the item contains all the item keywords.
	//	  
	//    No values are inlined, but instead replaced by placeholders ('?') to prevent injection by the user (see Bobby Tables).
	//
	// 2. Bind all the values. This has to be done in the same order as the placeholders appear in the string. So:
	//    1. Bind the min/maxDate parameters.
	//    2. Bind the keywords, one at a time (student first, them item)
	//
	// After all this, the query is returned, executed, and data is extracted from the results.

	// Note that, since no values are inlined, only the number of keywords actually needs to be known in order
	// to generate the string.
	auto queryString = createFilterQueryString(studentKeywords.count(), itemKeywords.count());
	auto query = QSqlQuery(queryString, database);

	bindDateRestriction(query, minDate, maxDate);
	bindKeywords(query, studentKeywords, itemKeywords);

	return query;
}

QString DatabaseHistory::createFilterQueryString(int studentKeywordCount, int itemKeywordCount) const
{
	QString queryString =
		"SELECT * FROM history "
		"WHERE dateTime >= ? " // This is the date/time restriction.
		"AND dateTime < ? "; // The first '?' is the minDate, the second is the maxDate.

	// For every keyword, add an additional restriction.
	for (int i = 0; i < studentKeywordCount; i++)
		queryString += "AND studentName || ' (' || studentId || ')' LIKE '%' || ? || '%' ";
	for (int i = 0; i < itemKeywordCount; i++)
		queryString += "AND itemName || ' #' || itemNumber LIKE '%' || ? || '%' ";

	return queryString;
}

void DatabaseHistory::bindDateRestriction(QSqlQuery &query, const QDate &minDate, const QDate &maxDate) const
{
	// By default, creating a date/time from a date will have a time at the beginning of the day.
	// One day is added to the max to extend it to the end of the day.
	// Otherwise, when the user tried to see all the entries for a single day (by settings the min and max the same)
	// nothing would show up because the query would be restricted to the same time as well as day.
	auto minDateAsNumber = QDateTime(minDate).toMSecsSinceEpoch();
	auto maxDateAsNumber = QDateTime(maxDate).addDays(1).toMSecsSinceEpoch();

	query.addBindValue(minDateAsNumber);
	query.addBindValue(maxDateAsNumber);
}
	
void DatabaseHistory::bindKeywords(QSqlQuery &query, const QStringList &studentKeywords, const QStringList &itemKeywords) const
{
	foreach (auto keyword, studentKeywords)
		query.addBindValue(keyword);

	foreach (auto keyword, itemKeywords)
		query.addBindValue(keyword);
}

QList<Entry> DatabaseHistory::extractOutput(QSqlQuery &query) const
{
	QList<Entry> output;
	while (query.next())
	{
		auto record = query.record();
		auto entry = extractEntry(record);

		output << entry;
	}

	return output;
}

Entry DatabaseHistory::extractEntry(QSqlRecord &record) const
{
	auto dateTime = record.value("dateTime").toLongLong();
	auto studentName = record.value("studentName").toString();
	auto studentNumber = record.value("studentId").toString();
	auto studentPassword = record.value("studentPassword").toString();
	auto itemName = record.value("itemName").toString();
	auto itemNumber = record.value("itemNumber").toInt();

	return Entry(
		QDateTime::fromMSecsSinceEpoch(dateTime),
		Student(studentName, studentNumber, studentPassword),
		Item(itemName, itemNumber));
}
